package tt.dz.dao.base;

import java.lang.reflect.Field;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.collections.map.ListOrderedMap;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.*;

import org.springframework.jdbc.object.SqlCall;
import org.springframework.jdbc.object.SqlFunction;
import org.springframework.jdbc.support.JdbcUtils;
import tt.dz.entity.station.OmsStation;
import tt.dz.util.Constants;
import tt.dz.util.ReflectUtils;
import tt.dz.util.annotation.AnnotationUtils;

/**
 * 数据库基础操作类
 * @author 谢龙飞
 * @modify pengyu
 */
@SuppressWarnings("unchecked")
public class BasisDao<T> {
	
	/**JDBC模板注入*/
	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	/**log4j日志 */
	private static final Log log = LogFactory.getLog(BasisDao.class);

	public JdbcTemplate getJdbcTemplate() {
//		log.debug("jdbcTemplate...............................................");
		return jdbcTemplate;
	}

	/***
	 * 保存对象，通用
	 * @param obj
	 * @return
	 */
	public int save(final Object obj)
	{

		//得到类信息
		final Class<?> clss = obj.getClass();
		//获取属性
		final Field fields[] = clss.getDeclaredFields();

		//获取映射表名称
		String tableName = AnnotationUtils.getEntityTableMappingByAnnotationName(clss);

		//参数值集合
		final List<Object> fieldValuesList = new ArrayList<Object>();

		//拼接插入语句
		StringBuffer sql = new StringBuffer("INSERT INTO ");
		sql.append(tableName);
		final StringBuffer fieldName = new StringBuffer(" (");
		final StringBuffer fieldValues = new StringBuffer("VALUES (");

		try
		{
			for (Field field : fields)
			{

				//判断是否含有不可用修饰符
				if (!ReflectUtils.judgeModifiersAvailable(field))
				{
					//是否为忽略映射属性
					if (!AnnotationUtils.ignore(field))
					{
						//调用get方法获取值
						Object fieldObj = ReflectUtils.invokeObjectReadMethodByFieldNameAndClass(obj, field.getName(), clss);
						if (fieldObj != null)
						{

							fieldName.append("`");
							fieldName.append(field.getName());
							fieldName.append("`");
							fieldName.append(",");
							//设置插入字段占位
							fieldValues.append(" ? ,");
							//当前位的值有序加入list
							fieldValuesList.add(fieldObj);
						}
					}
				}
			}

			fieldName.deleteCharAt(fieldName.length() - 1);
			fieldValues.deleteCharAt(fieldValues.length() - 1);
			fieldName.append(") ");
			fieldValues.append(");");
			sql.append(fieldName);
			sql.append(fieldValues);

			if (Constants.Jdbc.SHOW_SQL)
			{
				log.debug("[MyJDBCFrameWork]:" + sql);
				log.debug("[param]:" + fieldValuesList);
			}

			return getJdbcTemplate().update(sql.toString(), new PreparedStatementSetter()
			{
				@Override
				public void setValues(PreparedStatement ps) throws SQLException
				{
					try
					{
						//有序设置参数集合的参数值
						int size = fieldValuesList.size();
						for (int i = 0; i < size; i++)
						{
							ps.setObject(i + 1, fieldValuesList.get(i));
						}
					}
					catch (SQLException e)
					{
						log.error(e.getMessage(), e);
						throw e;
					}
				}
			});
		}
		catch (Exception e)
		{
			log.error(e.getMessage(), e);
			throw e;
		}

	}
	
	
	/***
	 * 批处理保存
	 * @param
	 * @return
	 */
	public void batchSave(final Class<T> clss,List<T> lst)throws Exception{
		
		
		//获取属性
		final Field fields[] = clss.getDeclaredFields();
		
		//获取映射表名称
		String tableName = AnnotationUtils.getEntityTableMappingByAnnotationName(clss);

		//参数值集合
		final List<Object[]> fieldValuesList = new ArrayList<Object[]>();
		
		//拼接插入语句
		StringBuffer sql = new StringBuffer("INSERT INTO ");
		sql.append(tableName);
		final StringBuffer fieldName = new StringBuffer(" (");
		final StringBuffer fieldValues = new StringBuffer("VALUES (");
		try 
		{
			for (Field field : fields) 
			{
				
				//判断是否含有不可用修饰符
				if(!ReflectUtils.judgeModifiersAvailable(field))
				{
					//是否为忽略映射属性
					if(!AnnotationUtils.ignore(field)){
						//调用get方法获取值
						fieldName.append("`");
						fieldName.append(field.getName());
						fieldName.append("`");
						fieldName.append(",");
						//设置插入字段占位
						fieldValues.append(" ? ,");
						
						Object[] addObjs = new Object[lst.size()];
						for (T obj :lst )
						{
							Object fieldObj = ReflectUtils.invokeObjectReadMethodByFieldNameAndClass(obj, field.getName(), clss);
							addObjs[lst.indexOf(obj)]= fieldObj;
							fieldValuesList.add(addObjs);
						}
						

					}
				}
			}

			fieldName.deleteCharAt(fieldName.length()-1);
			fieldValues.deleteCharAt(fieldValues.length()-1);
			fieldName.append(") ");
			fieldValues.append(");");
			sql.append(fieldName);
			sql.append(fieldValues);
			if(Constants.Jdbc.SHOW_SQL){
				log.debug("[MyJDBCFrameWork]:"+sql);
				log.debug("[param]:"+fieldValuesList);
			}
			getJdbcTemplate().batchUpdate(sql.toString(), fieldValuesList);
		} catch (Exception e) 
		{
			e.printStackTrace();log.error(e.getMessage(), e);
			throw e;
		}
		
		
	}
	
	/***
	 * 保存对象，通用
	 * @param obj
	 * @return
	 */
	public int saveList(final List<T> obj){
		
		if(obj != null){
			
			//拼接插入语句
			StringBuffer sql = new StringBuffer("INSERT INTO ");
			//属性
			final StringBuffer fieldName = new StringBuffer(" (");
			//插入值
			final StringBuffer fieldValues = new StringBuffer(" VALUES ");

			boolean firstAppend = true;
			
			//类信息
			Class<?> clss = null;
			
			String tableName = null;

			//参数值集合
			final List<Object> fieldValuesList = new ArrayList<Object>();
			
			for (Object object : obj) {
				//得到类信息
				if(clss==null){
					clss = object.getClass();
					//获取映射表名称
					tableName = AnnotationUtils.getEntityTableMappingByAnnotationName(clss);
					sql.append(tableName);
				}
				//获取属性
				final Field fields[] = clss.getDeclaredFields();
				
				fieldValues.append(" (");
				for (Field field : fields) {
					
					//判断是否含有不可用修饰符
					if(!ReflectUtils.judgeModifiersAvailable(field)){
						//是否为忽略映射属性
						if(!AnnotationUtils.ignore(field)){
							//调用get方法获取值
							Object fieldObj = ReflectUtils.invokeObjectReadMethodByFieldNameAndClass(object, field.getName(), clss);
							if(fieldObj!=null){
								//拼接一次属性就行
								if(firstAppend){
									fieldName.append("`");
									fieldName.append(field.getName());
									fieldName.append("`");
									fieldName.append(",");
								}
								
								//设置插入字段占位
								fieldValues.append(" ? ,");
								//当前位的值有序加入list
								fieldValuesList.add(fieldObj);
							}
						}
					}
				}
				fieldValues.deleteCharAt(fieldValues.length()-1);
				firstAppend = false;
				fieldValues.append("), ");
			}
			fieldName.deleteCharAt(fieldName.length()-1);
			fieldName.append(") ");
			fieldValues.replace(fieldValues.length()-2, fieldValues.length()-1, ";");
			sql.append(fieldName);
			sql.append(fieldValues);

			if(Constants.Jdbc.SHOW_SQL){
				log.debug("[MyJDBCFrameWork]:"+sql);
				log.debug("[param]:"+fieldValuesList);
			}
			
			return getJdbcTemplate().update(sql.toString(), new PreparedStatementSetter() {
				@Override
				public void setValues(PreparedStatement ps) throws SQLException {
					try {
						//有序设置参数集合的参数值
						int size = fieldValuesList.size();
						for (int i = 0; i < size; i++) {
							ps.setObject(i+1, fieldValuesList.get(i));
						}
					} catch (Exception e) {
						e.printStackTrace();log.error(e.getMessage(), e);
					}
				}
			});
		}
		else{
			return -1;
		}
	}
	
	
	/**
	 * 逻辑删除信息,通用
	 * @param obj
	 */
	public int LogicDeleteObject(final Object obj){
		
		//得到类信息
		final Class<?> cls = obj.getClass();
		
		//得到表名称
		String tableName = AnnotationUtils.getEntityTableMappingByAnnotationName(cls);

		//参数值集合
		final List<Object> fieldValuesList = new ArrayList<Object>();
		
		//开始拼接sql
		final StringBuffer sql = new StringBuffer("UPDATE ");
		sql.append(tableName);
		
		final Field[] fields = cls.getDeclaredFields();
		
		sql.append(" SET ");
		sql.append(Constants.Jdbc.VALID_FLAG);
		sql.append("=? ");
		boolean firstAppend = true;
		
		try {
			for (Field field : fields) {

				//判断是否含有不可用修饰符
				if(!ReflectUtils.judgeModifiersAvailable(field)){
					//得到主键注解  //排除序列id //排除引用对象
					if(!AnnotationUtils.ignore(field)
							&& AnnotationUtils.fieldIsPrimarkey(field)){
						//调用get方法
						Object fieldObj = ReflectUtils.invokeObjectReadMethodByFieldNameAndClass(obj, field.getName(), cls);
						if(fieldObj==null){
							throw new RuntimeException("单个对象删除所有主键列不可为空");
						}
						
						if(firstAppend){
							firstAppend=false;
							sql.append(" WHERE ");
						}
						else{
							sql.append(" AND ");
						}
						sql.append("`");
						sql.append(field.getName());
						sql.append("`");
						//拼接字段占位
						sql.append("=? ");
						//当前位的值有序加入list
						fieldValuesList.add(fieldObj);
					}
				}
			}
		} catch (Exception e) {
			e.printStackTrace();log.error(e.getMessage(), e);
			return -1;
		}

		if(Constants.Jdbc.SHOW_SQL){
			log.debug("[MyJDBCFrameWork]:"+sql);
			log.debug("[param]:"+fieldValuesList);
		}		
		
		return getJdbcTemplate().update(sql.toString(), new PreparedStatementSetter()  {

			@Override
			public void setValues(PreparedStatement ps) throws SQLException {
				try {
					//有序设置参数集合的参数值
					ps.setObject(1, 0);
					int size = fieldValuesList.size();
					for (int i = 0; i < size; i++) {
						ps.setObject(i+2, fieldValuesList.get(i));
	 				}
				} catch (Exception e) {
					e.printStackTrace();log.error(e.getMessage(), e);
				}
			}
		});
	}
	
	/**
	 * 修改信息，通用
	 * @param obj
	 * @return
	 */
	public int updateObject(final Object obj)
	{

		//得到对象类信息
		final Class<?> cls = obj.getClass();

		//获取表名称
		String tableName = AnnotationUtils.getEntityTableMappingByAnnotationName(cls);

		final Field[] fields = cls.getDeclaredFields();

		//参数值集合
		final List<Object> fieldValuesList = new ArrayList<Object>();
		//参数值集合
		final List<Object> whereValuesList = new ArrayList<Object>();
		//拼接修改语句
		StringBuffer mainSql = new StringBuffer("UPDATE ");
		//拼接修改条件
		StringBuffer whereSql = new StringBuffer();
		mainSql.append(tableName);
		mainSql.append(" SET ");

		boolean firstAppend = true;//首次拼接

		try
		{
			for (Field field : fields)
			{

				//判断是否含有不可用修饰符
				if (!ReflectUtils.judgeModifiersAvailable(field))
				{
					//是否为忽略映射属性
					if (!AnnotationUtils.ignore(field))
					{
						//调用get方法
						Object fieldObj = ReflectUtils.invokeObjectReadMethodByFieldNameAndClass(obj, field.getName(), cls);
						//如果返回值为不为空代表有值，拼接update
						if (fieldObj != null && !AnnotationUtils.fieldIsPrimarkey(field))
						{
							String fieldName = field.getName();
							mainSql.append("`");
							mainSql.append(fieldName);
							mainSql.append("`");
							//拼接字段占位
							mainSql.append("=?,");
							//当前位的值有序加入list
							fieldValuesList.add(fieldObj);
						}
					}
				}
				//如果为主键列
				if (AnnotationUtils.fieldIsPrimarkey(field))
				{
					//调用get方法
					Object fieldObj = ReflectUtils.invokeObjectReadMethodByFieldNameAndClass(obj, field.getName(), cls);
					if (fieldObj == null)
					{
						throw new RuntimeException("单对象修改所有主键列不可为空");
					}
					else
					{
						if (firstAppend)
						{
							firstAppend = false;
							whereSql.append(" WHERE ");
						}
						else
						{
							whereSql.append(" AND ");
						}

						whereSql.append(field.getName());
						//拼接字段占位
						whereSql.append("=? ");
						//当前位的值有序加入list
						whereValuesList.add(fieldObj);
					}
				}
			}

		}
		catch (Exception e)
		{
			e.printStackTrace();
			log.error(e.getMessage(), e);
		}
		mainSql.deleteCharAt(mainSql.length() - 1);

		if (Constants.Jdbc.SHOW_SQL)
		{
			log.debug("[MyJDBCFrameWork]:" + (mainSql.append(whereSql)));
			log.debug("[param]:" + fieldValuesList + whereValuesList);
			
		}

		try
		{
			return getJdbcTemplate().update(mainSql.toString(), new PreparedStatementSetter()
			{

				@Override
				public void setValues(PreparedStatement ps) throws SQLException
				{
					try
					{
						//有序设置参数集合的参数值
						int size = fieldValuesList.size();
						for (int i = 0; i < size; i++)
						{
							ps.setObject(i + 1, fieldValuesList.get(i));
						}
						int size1 = whereValuesList.size();
						for (int i = 0; i < size1; i++)
						{
							ps.setObject(i + 1 + size, whereValuesList.get(i));
						}
					}
					catch (Exception e)
					{
						e.printStackTrace();
						log.error(e.getMessage(), e);
					}
				}
			});
		}
		catch (Exception e)
		{
			log.error(e.getMessage(), e);
			throw  e;
		}
	}
	
	/**
	 * 通用查询方法
	 * @param cls 类
	 * @param paramsMap 有序参数Map
	 * @param pageNo 页码索引
	 * @param pageSize 每页个数
	 * @param sortName 排序列名
	 * @param sortVal 排序方式 1 正序 其他倒序
	 * @return
	 */
	public List<T> findListByClass(final Class<?> cls, final ListOrderedMap paramsMap,Integer pageNo,
			final Integer pageSize, final String sortName, final Integer sortVal){
		
		//获取表名称
		String tableName = AnnotationUtils.getEntityTableMappingByAnnotationName(cls);

		//开始拼接sql
		StringBuffer sqlBuffer = new StringBuffer("SELECT ");
		
		final Field[] fields = cls.getDeclaredFields();

		//设置查询列
		sqlBuffer.append(ReflectUtils.setQueryColumn(fields, tableName));
		
		sqlBuffer.append(" FROM ");
		sqlBuffer.append(tableName);

		boolean firstAppend = true;		

		//循环拼接引用查询的映射列条件（链接查询条件）
		if(paramsMap!=null){
			for(int i=0;i<paramsMap.size();i++){
				if(firstAppend){
					firstAppend = false;
					sqlBuffer.append(" WHERE ");
				}
				else{
					sqlBuffer.append(" AND ");
				}
				//tableName（主表）表的条件
				sqlBuffer.append("`");
				sqlBuffer.append(paramsMap.get(i));
				sqlBuffer.append("`");
				sqlBuffer.append("=? ");
			}
		}
		
		//排序匹配
		if(sortName!=null && !"".equals(sortName)){
			sqlBuffer.append(" ORDER BY ");
			//tableName（主表）表的排序条件
			sqlBuffer.append(" ");
			sqlBuffer.append(sortName);
			sqlBuffer.append(" ");
			if(sortVal==1){
				sqlBuffer.append(" ASC");
			}
			else{
				sqlBuffer.append(" DESC");
			}
		}
		
		//分页查询匹配
		if(pageNo!=null && pageSize!=null && pageSize.intValue()!=0){
			sqlBuffer.append(" LIMIT ");
			sqlBuffer.append((pageNo--)*pageSize);
			sqlBuffer.append(",");
			sqlBuffer.append(pageSize);
		}
		
		if(Constants.Jdbc.SHOW_SQL){
			log.debug("[MyJDBCFrameWork]:"+sqlBuffer);
			log.debug("[param]:"+paramsMap);
		}
		
		List<T> resultList = getJdbcTemplate().query(sqlBuffer.toString(),new PreparedStatementSetter(){
		    @Override
		    public void setValues(PreparedStatement ps) throws SQLException {
		    	if(paramsMap!=null){
					//有序设置参数集合的参数值
			    	for(int i=0;i<paramsMap.size();i++){
						ps.setObject(i+1, paramsMap.getValue(i));
					}
		    	}
		    }
	    }, new BeanPropertyRowMapper<T>((Class<T>) cls));
		
		/*//关联引用对象子查询
		try {
			if(AnnotationUtils.entityNeedReferenceQuery(cls)){
				//循环查询集合
				for (T t : resultList) {
					//获取对象所有属性
					Field tfields[] = t.getClass().getDeclaredFields();
					//循环属性
					for (Field tfield : tfields) {
						//获取引用注解
						Reference reference = AnnotationUtils.reference(tfield);
						if(reference!=null){
							//获取引用的类
							Class<?> recls = reference.target();
							//获取引用的列名
							String column = reference.column();
							ListOrderedMap referenceParamMap = new ListOrderedMap();
							
							//得到引用列的值
							Object obj = ReflectUtils.invokeObjectReadMethodByFieldNameAndClass(t, column, recls);
							
							referenceParamMap.put(column, obj);
							List<?> rList = this.findListByClass(recls, referenceParamMap, null, null, null, null);
							if(rList.size()==1){
								//当前类属性描述符
								PropertyDescriptor mainDescriptor = new PropertyDescriptor(tfield.getName(), cls);
								mainDescriptor.getWriteMethod().invoke(t, rList.get(0));
							}
						}
					}
				}
			}
		} catch (Exception e) {
			e.printStackTrace();log.error(e.getMessage(), e);
		}*/
		
		return resultList;
	}
	
	/**
	 * 通过对象查询匹配数据
	 * @param pageNo 页码 第一页开始
	 * @param pageSize 每页个数
	 * @param sortName 排序列名
	 * @param sortVal 排序方式 1 正序 其他倒序
	 * @return
	 */
	public List<T> findExample(final Object obj,Integer pageNo,
			final Integer pageSize, final String sortName, final Integer sortVal){
		
		//得到类信息
		Class<?> cls = obj.getClass();
		//所有属性
		Field fields[] =  cls.getDeclaredFields();
		//获取表名称
		String tableName = AnnotationUtils.getEntityTableMappingByAnnotationName(cls);
		
		StringBuffer buffer = new StringBuffer("SELECT ");
		buffer.append(ReflectUtils.setQueryColumn(fields, tableName));
		buffer.append(" FROM ");
		buffer.append(tableName);
		

		//是否首次追加条件
		boolean firstAppend = true;
		
		//参数值
		final List<Object> queryParamList = new ArrayList<Object>();
		//循环属性追加条件
		for (Field field : fields) {

			//判断是否含有不可用修饰符
			if(!ReflectUtils.judgeModifiersAvailable(field)){
				//是否为忽略映射属性
				if(!AnnotationUtils.ignore(field)){
					//调用get方法
					Object resultObj = ReflectUtils.invokeObjectReadMethodByFieldNameAndClass(obj, field.getName(), cls);
					if(resultObj!=null && !"".equals(resultObj)){
						
						//首次追加where条件
						if(firstAppend){
							firstAppend = false;
							buffer.append(" WHERE ");
						}
						else{
	
							buffer.append(" AND ");
						}
						if(AnnotationUtils.isQueryByLike(field))
						{
							buffer.append("`");
							buffer.append(field.getName());
							buffer.append("`");
							buffer.append("like ?");
							StringBuilder likeValue = new StringBuilder();
							likeValue.append("%").append(resultObj).append("%");
							//将参数值顺序保存至list
							queryParamList.add(likeValue.toString());
						}
						else
						{
							buffer.append("`");
							buffer.append(field.getName());
							buffer.append("`");
							buffer.append("=?");
							//将参数值顺序保存至list
							queryParamList.add(resultObj);
						}
					}
				}
			}
		}
		
		//排序匹配
		if(sortName!=null && !"".equals(sortName)){
			buffer.append(" ORDER BY ");
			//tableName（主表）表的排序条件
			buffer.append(" ");
			buffer.append(sortName);
			buffer.append(" ");
			if(sortVal==1){
				buffer.append(" ASC");
			}
			else{
				buffer.append(" DESC");
			}
		}
		
		//分页查询匹配
		if(pageNo!=null && pageSize!=null && pageSize.intValue()!=0){
			buffer.append(" LIMIT ");
			buffer.append((pageNo-1)*pageSize);
			buffer.append(",");
			buffer.append(pageSize);
		}
		
		if(Constants.Jdbc.SHOW_SQL){
			log.debug("[MyJDBCFrameWork]:"+buffer);
			log.debug("[param]:"+queryParamList);
		}
		return getJdbcTemplate().query(buffer.toString(), new PreparedStatementSetter() {
			
			@Override
			public void setValues(PreparedStatement ps) throws SQLException {
				
				//设置参数
				for (int i=0;i<queryParamList.size();i++) {
					Object obj = queryParamList.get(i);
					ps.setObject(i+1, obj);
				}
			}
		}, new BeanPropertyRowMapper<T>((Class<T>) cls));
	}
	
	/**
	 * 
	 * @param obj
	 * @param pageNo
	 * @param pageSize
	 * @param sortName
	 * @param sortVal
	 * @param tableName
	 * @param queryParamList
	 * @return
	 */
	public List<T> findExampleByTableName(final Object obj,Integer pageNo,
			final Integer pageSize, final String sortName, 
			final Integer sortVal,String tableName,
			final List<Object> queryParamList){
		
		//得到类信息
		Class<?> cls = obj.getClass();
		//所有属性
		Field fields[] =  cls.getDeclaredFields();
		//获取表名称
//		String tableName = AnnotationUtils.getEntityTableMappingByAnnotationName(cls);
		
		StringBuffer buffer = new StringBuffer("SELECT ");
		buffer.append(ReflectUtils.setQueryColumn(fields, tableName));
		buffer.append(" FROM ");
		buffer.append(tableName);
		

		//是否首次追加条件
		boolean firstAppend = true;
		
		//参数值
//		final List<Object> queryParamList = new ArrayList<Object>();
		//循环属性追加条件
		for (Field field : fields) {

			//判断是否含有不可用修饰符
			if(!ReflectUtils.judgeModifiersAvailable(field)){
				//是否为忽略映射属性
				if(!AnnotationUtils.ignore(field)){
					//调用get方法
					Object resultObj = ReflectUtils.invokeObjectReadMethodByFieldNameAndClass(obj, field.getName(), cls);
					if(resultObj!=null && !"".equals(resultObj)){
						
						//首次追加where条件
						if(firstAppend){
							firstAppend = false;
							buffer.append(" WHERE ");
						}
						else{
	
							buffer.append(" AND ");
						}
						if(AnnotationUtils.isQueryByLike(field))
						{
							buffer.append("`");
							buffer.append(field.getName());
							buffer.append("`");
							buffer.append("like ?");
							StringBuilder likeValue = new StringBuilder();
							likeValue.append("%").append(resultObj).append("%");
							//将参数值顺序保存至list
							queryParamList.add(likeValue.toString());
						}
						else
						{
							buffer.append("`");
							buffer.append(field.getName());
							buffer.append("`");
							buffer.append("=?");
							//将参数值顺序保存至list
							queryParamList.add(resultObj);
						}
					}
				}
			}
		}
		
		//排序匹配
		if(sortName!=null && !"".equals(sortName)){
			buffer.append(" ORDER BY ");
			//tableName（主表）表的排序条件
			buffer.append(" ");
			buffer.append(sortName);
			buffer.append(" ");
			if(sortVal==1){
				buffer.append(" ASC");
			}
			else{
				buffer.append(" DESC");
			}
		}
		
		//分页查询匹配
		if(pageNo!=null && pageSize!=null && pageSize.intValue()!=0){
			buffer.append(" LIMIT ");
			buffer.append((pageNo--)*pageSize);
			buffer.append(",");
			buffer.append(pageSize);
		}
		
		if(Constants.Jdbc.SHOW_SQL){
			log.debug("[MyJDBCFrameWork]:"+buffer);
			log.debug("[param]:"+queryParamList);
		}
		return getJdbcTemplate().query(buffer.toString(), new PreparedStatementSetter() {
			
			@Override
			public void setValues(PreparedStatement ps) throws SQLException {
				
				//设置参数
				for (int i=0;i<queryParamList.size();i++) {
					Object obj = queryParamList.get(i);
					ps.setObject(i+1, obj);
				}
			}
		}, new BeanPropertyRowMapper<T>((Class<T>) cls));
	}
	/**
	 * 不用自己加where的sql
	 * @param obj
	 * @param pageNo
	 * @param pageSize
	 * @param sortName
	 * @param sortVal
	 * @param sql
	 * @param queryParamList
	 * @return
	 */
	public List<T> findExampleBySql(final Object obj,Integer pageNo,
			final Integer pageSize, final String sortName, 
			final Integer sortVal,String sql,
			final List<Object> queryParamList){
		
		//得到类信息
		Class<?> cls = obj.getClass();
		//所有属性
		Field fields[] =  cls.getDeclaredFields();
		StringBuffer buffer = new StringBuffer(sql);
		//循环属性追加条件
		for (Field field : fields) {

			//判断是否含有不可用修饰符
			if(!ReflectUtils.judgeModifiersAvailable(field)){
				//是否为忽略映射属性
				if(!AnnotationUtils.ignore(field)){
					//调用get方法
					Object resultObj = ReflectUtils.invokeObjectReadMethodByFieldNameAndClass(obj, field.getName(), cls);
					if(resultObj!=null && !"".equals(resultObj)){
						buffer.append(" AND ");
						buffer.append("`");
						buffer.append(field.getName());
						buffer.append("`");
						buffer.append("=?");
						//将参数值顺序保存至list
						queryParamList.add(resultObj);
					}
				}
			}
		}
		
		//排序匹配
		if(sortName!=null && !"".equals(sortName)){
			buffer.append(" ORDER BY ");
			//tableName（主表）表的排序条件
			buffer.append(" ");
			buffer.append(sortName);
			buffer.append(" ");
			if(sortVal==1){
				buffer.append(" ASC");
			}
			else{
				buffer.append(" DESC");
			}
		}
		
		//分页查询匹配
		if(pageNo!=null && pageSize!=null && pageSize.intValue()!=0){
			buffer.append(" LIMIT ");
			buffer.append((pageNo--)*pageSize);
			buffer.append(",");
			buffer.append(pageSize);
		}
		
		if(Constants.Jdbc.SHOW_SQL){
			log.debug("[MyJDBCFrameWork]:"+buffer);
			log.debug("[param]:"+queryParamList);
		}
		return getJdbcTemplate().query(buffer.toString(), new PreparedStatementSetter() {
			
			@Override
			public void setValues(PreparedStatement ps) throws SQLException {
				
				//设置参数
				for (int i=0;i<queryParamList.size();i++) {
					Object obj = queryParamList.get(i);
					ps.setObject(i+1, obj);
				}
			}
		}, new BeanPropertyRowMapper<T>((Class<T>) cls));
	}
	
	/**
	 * 通过sql查询数据返回List集合
	 * @param sql
	 * @param params
	 * @return
	 */
	public List<Map<String,Object>> findDataBySql(String sql,final Object ...params){
		
		if(Constants.Jdbc.SHOW_SQL){
			log.debug("[MyJDBCFrameWork]:"+sql);
			if(params!=null){
				log.debug("[param]:"+Arrays.asList(params));
			}
		}
		
		return getJdbcTemplate().query(sql, new PreparedStatementSetter() {
			
			@Override
			public void setValues(PreparedStatement ps) throws SQLException {
				
				if(params!=null && params.length>0){
					//设置参数
					for (int i=0;i<params.length;i++) {
						ps.setObject(i+1, params[i]);
					}
				}
			}
		}, new ColumnMapRowMapper());
	}
	
	/**
	 * 通过sql查询封装至实体
	 * @param sql
	 * @param cls
	 * @param params
	 * @return
	 */
	public List<T> findDataBySqlOfClass(final String sql,final Class<?> cls,final Object ...params){
		
		if(Constants.Jdbc.SHOW_SQL){
			log.debug("[MyJDBCFrameWork]:"+sql);
			if(params!=null){
				log.debug("[param]:"+Arrays.asList(params));
			}
		}
		
		return getJdbcTemplate().query(sql.toString(), new PreparedStatementSetter() {
			
			@Override
			public void setValues(PreparedStatement ps) throws SQLException {
				
				//设置参数
				if(params!=null){
					for (int i=0;i<params.length;i++) {
						Object obj = params[i];
						ps.setObject(i+1, obj);
					}
				}
			}
		}, new BeanPropertyRowMapper<T>((Class<T>) cls));
	}
	public static void main(String[] args) {

		OmsStation omsStation = new OmsStation();
		omsStation.setStation_id("1");
		omsStation.setAddress("cd");
		new BasisDao<>().updateObject(omsStation);
	}
}

